﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recSectorRegistrationRepo : IRepository<recSectorRegistration, int>
    {
        public recSectorRegistration GetByIdForUpdateStatus(int key)
        {
            recSectorRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                StringBuilder sbQuery = new StringBuilder();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    sbQuery.AppendFormat("SELECT trs.SectorRegistrationID, trs.SectorID, trs.SubSectorID, trs.SubSubSectorID, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("trs.IsApproved, trs.ApprovedDate, trs.ApprovedBy, trs.InvoiceNo, trs.InvoiceDate, trs.InvoiceValue, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("trs.PaymentNo, trs.PaymentDate, trs.PaymentValue, trs.RegistrationStatusID, trs.DebtorSectorID, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("trs.RegistrationID, trs.RegistrationDate,trs.EmisionTypeID, trs.EmisionValue, trs.CreatedDate, {0}", Environment.NewLine);
                    sbQuery.AppendFormat("trs.CreatedBy, trs.UpdatedDate, trs.UpdatedBy,mrr.RegistrationStatusName {0}", Environment.NewLine);
                    sbQuery.AppendFormat("FROM transactions.recSectorRegistration trs {0}", Environment.NewLine);
                    sbQuery.AppendFormat("inner join master.recRegistrationStatus mrr on mrr.RegistrationStatusID=trs.RegistrationStatusID {0}", Environment.NewLine);
                    sbQuery.AppendFormat("WHERE trs.SectorRegistrationID = @SectorRegistrationID {0}", Environment.NewLine);
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SectorRegistrationID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recSectorRegistration(true);
                                t.SectorRegistrationID = dr[recSectorRegistration.Fields.SectorRegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SectorRegistrationID].ToString());
                                t.SectorID = dr[recSectorRegistration.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recSectorRegistration.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recSectorRegistration.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SubSubSectorID].ToString());
                                t.IsApproved = dr[recSectorRegistration.Fields.IsApproved] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSectorRegistration.Fields.IsApproved].ToString());
                                t.ApprovedDate = dr[recSectorRegistration.Fields.ApprovedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.ApprovedDate].ToString());
                                t.ApprovedBy = dr[recSectorRegistration.Fields.ApprovedBy] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.ApprovedBy].ToString();
                                t.InvoiceNo = dr[recSectorRegistration.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recSectorRegistration.Fields.InvoiceDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recSectorRegistration.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.InvoiceValue].ToString());
                                t.PaymentNo = dr[recSectorRegistration.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recSectorRegistration.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recSectorRegistration.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.PaymentValue].ToString());
                                t.RegistrationStatusID = dr[recSectorRegistration.Fields.RegistrationStatusID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.RegistrationStatusID].ToString());
                                t.DebtorSectorID = dr[recSectorRegistration.Fields.DebtorSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recSectorRegistration.Fields.DebtorSectorID].ToString());
                                t.RegistrationID = dr[recSectorRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.RegistrationID].ToString());
                                t.RegistrationDate = dr[recSectorRegistration.Fields.RegistrationDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.RegistrationDate].ToString());
                                t.EmisionTypeID = dr[recSectorRegistration.Fields.EmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recSectorRegistration.Fields.EmisionTypeID].ToString());
                                t.EmisionValue = dr[recSectorRegistration.Fields.EmisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.EmisionValue].ToString());
                                t.CreatedDate = dr[recSectorRegistration.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recSectorRegistration.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recSectorRegistration.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recSectorRegistration.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.UpdatedBy].ToString();
                                t.recRegistrationStatus.RegistrationStatusName = dr[recRegistrationStatus.Fields.RegistrationStatusName] == DBNull.Value ? string.Empty : dr[recRegistrationStatus.Fields.RegistrationStatusName].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recSectorRegistration GetByInvoiceNo(string InvoiceNo)
        {
            recSectorRegistration t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT SectorRegistrationID, SectorID, SubSectorID, SubSubSectorID, BillingNo, IsApproved, ApprovedDate, ApprovedBy, InvoiceNo, InvoiceDate, InvoiceValue, PaymentNo, PaymentDate, PaymentValue, RegistrationStatusID, DebtorSectorID, RegistrationID, RegistrationDate, EmisionTypeID, EmisionValue, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, RegistrationEmisionTypeID FROM transactions.recSectorRegistration WHERE InvoiceNo = @InvoiceNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recSectorRegistration();
                                t.SectorRegistrationID = dr[recSectorRegistration.Fields.SectorRegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SectorRegistrationID].ToString());
                                t.SectorID = dr[recSectorRegistration.Fields.SectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SectorID].ToString());
                                t.SubSectorID = dr[recSectorRegistration.Fields.SubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SubSectorID].ToString());
                                t.SubSubSectorID = dr[recSectorRegistration.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.SubSubSectorID].ToString());
                                t.BillingNo = dr[recSectorRegistration.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.BillingNo].ToString();
                                t.IsApproved = dr[recSectorRegistration.Fields.IsApproved] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSectorRegistration.Fields.IsApproved].ToString());
                                t.ApprovedDate = dr[recSectorRegistration.Fields.ApprovedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.ApprovedDate].ToString());
                                t.ApprovedBy = dr[recSectorRegistration.Fields.ApprovedBy] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.ApprovedBy].ToString();
                                t.InvoiceNo = dr[recSectorRegistration.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recSectorRegistration.Fields.InvoiceDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.InvoiceDate].ToString());
                                t.InvoiceValue = dr[recSectorRegistration.Fields.InvoiceValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.InvoiceValue].ToString());
                                t.PaymentNo = dr[recSectorRegistration.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recSectorRegistration.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.PaymentDate].ToString());
                                t.PaymentValue = dr[recSectorRegistration.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.PaymentValue].ToString());
                                t.RegistrationStatusID = dr[recSectorRegistration.Fields.RegistrationStatusID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.RegistrationStatusID].ToString());
                                t.DebtorSectorID = dr[recSectorRegistration.Fields.DebtorSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recSectorRegistration.Fields.DebtorSectorID].ToString());
                                t.RegistrationID = dr[recSectorRegistration.Fields.RegistrationID] == DBNull.Value ? 0 : int.Parse(dr[recSectorRegistration.Fields.RegistrationID].ToString());
                                t.RegistrationDate = dr[recSectorRegistration.Fields.RegistrationDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.RegistrationDate].ToString());
                                t.EmisionTypeID = dr[recSectorRegistration.Fields.EmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recSectorRegistration.Fields.EmisionTypeID].ToString());
                                t.EmisionValue = dr[recSectorRegistration.Fields.EmisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recSectorRegistration.Fields.EmisionValue].ToString());
                                t.CreatedDate = dr[recSectorRegistration.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recSectorRegistration.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recSectorRegistration.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSectorRegistration.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recSectorRegistration.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.UpdatedBy].ToString();
                                t.RegistrationEmisionTypeID = dr[recSectorRegistration.Fields.RegistrationEmisionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recSectorRegistration.Fields.RegistrationEmisionTypeID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public List<recSectorRegistration> GetList(int registrationid, int debtorid, string subsubsectorids)
        {
            List<recSectorRegistration> tt = new List<recSectorRegistration>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    //cmd.CommandText = "select InvoiceNo from transactions.recSectorRegistration where RegistrationID=@RegistrationId and SubSubSectorID in (" + subsubsectorids + ") and SubSubSectorID not in (SELECT SubSubSectorID FROM master.recDebtorSector WHERE DebtorID = @DebtorId AND SubSubSectorID in(" + subsubsectorids + "))";
                    cmd.CommandText = "select InvoiceNo from transactions.recSectorRegistration where RegistrationID=@RegistrationId and SubSubSectorID in (" + subsubsectorids + ")";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RegistrationId", SqlDbType.Int).Value = registrationid;
                    cmd.Parameters.Add("@DebtorId", SqlDbType.Int).Value = debtorid;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recSectorRegistration t = null;
                                t = new recSectorRegistration();
                                t.InvoiceNo = dr[recSectorRegistration.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSectorRegistration.Fields.InvoiceNo].ToString();
                                tt.Add(t);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
    }
}
